Workbook |
| The Excel Application has a collection of Workbooks. Each Workbook has a collection of Worksheets. Each Worksheet has a collection of cells (a Range object). A range objet can be a single cell, a rectangular block of cells, or la union of many rectangular blocks (a non-contiguous range). The figures below show these Excel components. La aplicación de Excel tiene una colección de Workbooks. Cada Workbook tiene una colección de Worksheets. Cada Worksheet tiene una colección de celdas (un objeto Range). Un objeto Range puede ser una sola celda, un bloque rectangular de celdas, o la unión de varios bloques rectangulares (un rango no contiguo). Las figuras de abajo muestra cada uno de estos elementos. |
| Problem 1 |
| Create a Dialog application called ExcelWrite using Wintempla to write a number in a cell in Microsoft Excel. Cree una aplicación de Diálogo llamada ExcelWrite usando Wintempla para escribir un número en una celda de Microsoft Excel. |
| ExcelWrite.h |
| #pragma once //______________________________________ ExcelWrite.h #include "resource.h" class ExcelWrite: public Win::Dialog { public: ExcelWrite() { ::CoInitialize(NULL); } ~ExcelWrite() { ::CoUninitialize(); } protected: ... }; |
| ExcelWrite.cpp |
| ... void ExcelWrite::Window_Open(Win::Event& e) { Com::Object Application; try { Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //Com::Container::DisplayInterfaceFunctions(hWnd, Application); //______________________________________________________________ Get Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //_____________________________________________________________ Add a Workbook Com::Object Workbook; Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167 ////____________________________________________________________ Get Sheets //Com::Object Sheets; //Application.Get(L"Sheets", Sheets); ////____________________________________________________________ Get one Sheet //Com::Object Sheet; //Sheets.Get(L"Item", 1, Sheet); // The first sheet //Sheet.Method(L"Select"); //____________________________________________________________ Get the active Sheet Com::Object ActiveSheet; Application.Get(L"ActiveSheet", ActiveSheet); //____________________________________________________________ Put the name of the Sheet ActiveSheet.Put(L"Name", L"Accounting"); //____________________________________________________________ Get the A1 Cell Com::Object Range; ActiveSheet.Get(L"Range", L"A1", Range); //_____________________________________________________________ Set the value of the A1 Cell Range.Put(L"Value2", L"123.456"); //_____________________________________________________________ Save the file //Com::Container::DisplayInterfaceFunctions(hWnd, Book); _variant_t result; Workbook.Method(L"SaveAs", L"C:\\selo\\110\\info.xlsx", // Filename (short)51, //Excel.XlFileFormat.xlOpenXMLWorkbook = 51 L"", //Password L"", //WriteResPassword false, //ReadOnlyRecommended false, //CreateBackup true, //AccessMode //ConflictResolution //AddToMru //TextCodepage //TextVisualLayout //Local result); Application.Method(L"Quit"); } catch(Com::Exception excep) { excep.Display(hWnd, L"ExcelWrite"); Application.Method(L"Quit"); } } |
Active Sheet |
| The Workbooks has a collection of Sheets and only one Sheet is active. The following code illustrates how to activate a specific Sheet. Los Workbooks tienen tiene una colección de Sheets y solamente una Sheet está activa. El siguiente código ilustra como activar una Sheet específica. |
| Program.cpp |
| //Com::Object Sheets = Workbooks->get_Item(L"Report"); Com::Object Sheets = Workbooks->get_Item(3); Sheets.Method(L"Select"); |
| Problem 2 |
| Modify the program of Problem 1 to illustrate how to select cells. Modifique el programa del Problema 1 para ilustrar como seleccionar celdas. |
| ExcelWrite.cpp |
| ... void ExcelWrite::Window_Open(Win::Event& e) { Com::Object Application; Com::Object Range; try { Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //Com::Container::DisplayInterfaceFunctions(hWnd, Application); //________________________________________________ Get Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //________________________________________________ Add a Workbook Com::Object Workbook; Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167 //____________________________________________________________ Get the active Sheet Com::Object ActiveSheet; Application.Get(L"ActiveSheet", ActiveSheet); //____________________________________________________________ Set the value of the A1 Cell ActiveSheet.Get(L"Range", L"A1", Range); Range.Put(L"Value2", L"10"); //____________________________________________________________ Set the value of the B1 Cell ActiveSheet.Get(L"Range", L"B1", Range); Range.Put(L"Value2", L"20"); //____________________________________________________________ Set the value of the C1 Cell ActiveSheet.Get(L"Range", L"C1", Range); Range.Put(L"Value2", L"30"); //____________________________________________________________ Select A1 and B1 ActiveSheet.Get(L"Range", L"A1", L"B1", Range); Range.Method(L"Select"); } catch(Com::Exception excep) { excep.Display(hWnd, L"ExcelWrite"); Application.Method(L"Quit"); } } |
| Tip |
| A Range can also be seen as a matrix, in this case, the first parameter and second parameter of Range can be integer values starting at index 1. Un Range puede también verse como una matriz, en este caso, el primer y segundo parámetro de Range pueden ser valores enteros empezando en el índice 1. |
| Problem 3 |
| Test the following code. Pruebe el siguiente código. |
| ExcelWrite.cpp |
| ... void ExcelWrite::Window_Open(Win::Event& e) { Com::Object Application; Com::Object Range; Com::Object Cells; Com::Object Item; try { Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //Com::Container::DisplayInterfaceFunctions(hWnd, Application); //________________________________________________ Get Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //________________________________________________ Add a Workbook Com::Object Workbook; Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167 //____________________________________________________________ Get the active Sheet Com::Object ActiveSheet; Application.Get(L"ActiveSheet", ActiveSheet); //____________________________________________________________ Set the value of the A1 Cell ActiveSheet.Get(L"Range", L"A1", Range); Range.Put(L"Value2", L"10"); //____________________________________________________________ Set the value of the B1 Cell ActiveSheet.Get(L"Range", L"B1", Range); Range.Put(L"Value2", L"20"); //____________________________________________________________ Set the value of the C1 Cell ActiveSheet.Get(L"Range", L"C1", Range); Range.Put(L"Value2", L"30"); //____________________________________________________________ Get the Cells.Item(1, 1) ActiveSheet.Get(L"Cells", Cells); Cells.Get(L"Item", 1, 1, Item); Item.Put(L"Value2", L"Hello"); } catch(Com::Exception excep) { excep.Display(hWnd, L"ExcelWrite"); Application.Method(L"Quit"); } } |
| Problem 4 |
| Modify the program of Problem 1 to illustrate how to change the color of a cell. Modifique el programa del Problema 1 para ilustrar como cambiar el color de una celda. |
| ExcelWrite.cpp |
| ... void ExcelWrite::Window_Open(Win::Event& e) { Com::Object Application; Com::Object Range; Com::Object Cells; Com::Object Item; Com::Object Font; try { Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //Com::Container::DisplayInterfaceFunctions(hWnd, Application); //________________________________________________ Get Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //________________________________________________ Add a Workbook Com::Object Workbook; Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167 //____________________________________________________________ Get the active Sheet Com::Object ActiveSheet; Application.Get(L"ActiveSheet", ActiveSheet); //____________________________________________________________ Set the value of the A1 Cell ActiveSheet.Get(L"Range", L"A1", Range); Range.Put(L"Value2", L"10"); //_____________________________________________________________ Get the Cells ActiveSheet.Get(L"Cells", Cells); //_____________________________________________________________ Get the First Cell Cells.Get(L"Item", 1, 1, Item); //_____________________________________________________________ Get the Item Font Item.Get(L"Font", Font); //_____________________________________________________________ Set the Font Color Font.Put(L"ColorIndex", 3); } catch(Com::Exception excep) { excep.Display(hWnd, L"ExcelWrite"); Application.Method(L"Quit"); } } |
| Problem 5 |
| Modify the program of Problem 1 to illustrate how to get the number of rows and columns in a Worksheet. Modifique el programa del Problema 1 para ilustrar como obtener el número de renglones y columnas en una Worksheet. |
| ExcelWrite.cpp |
| ... void ExcelWrite::Window_Open(Win::Event& e) { Com::Object Application; Com::Object Range; Com::Object Cells; try { Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //Com::Container::DisplayInterfaceFunctions(hWnd, Application); //________________________________________________ Get Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //________________________________________________ Add a Workbook Com::Object Workbook; Workbooks.Method(L"Add", (long)-4167, Workbook); // Excel.XlSheetType.xlWorksheet = -4167 //____________________________________________________________ Get the active Sheet Com::Object ActiveSheet; Application.Get(L"ActiveSheet", ActiveSheet); //____________________________________________________________ Set the value of the cells ActiveSheet.Get(L"Range", L"A1", Range); Range.Put(L"Value2", L"100"); // ActiveSheet.Get(L"Range", L"B1", Range); Range.Put(L"Value2", L"101"); // ActiveSheet.Get(L"Range", L"C1", Range); Range.Put(L"Value2", L"102"); // ActiveSheet.Get(L"Range", L"A2", Range); Range.Put(L"Value2", L"200"); // ActiveSheet.Get(L"Range", L"B2", Range); Range.Put(L"Value2", L"201"); // ActiveSheet.Get(L"Range", L"C2", Range); Range.Put(L"Value2", L"202"); //_____________________________________________________________ Get the Cells ActiveSheet.Get(L"Cells", Cells); //_____________________________________________________________ Get the SpecialCells Cells.Method(L"SpecialCells", (short)11, Range); //Excel.XlCellType.xlCellTypeLastCell = 11 _variant_t numRows; _variant_t numCols; Range.Get(L"Row", numRows); Range.Get(L"Column", numCols); wstring text; Sys::Format(text, L"Rows = %d\r\nCols = %d", (short)numRows, (short)numCols); this->MessageBox(text, L"ExcelWrite", MB_OK); } catch(Com::Exception excep) { excep.Display(hWnd, L"ExcelWrite"); Application.Method(L"Quit"); } } |
Open file |
| The following code illustrates how to open a Microsoft Excel File. El código siguiente ilustra como abrir un Archivo de Microsoft Excel. |
| Program.cpp |
| Com::Object Application; const wchar_t* filename = L"C:\\Users\\John\\sales.xlsx"; try { //_______________________________________________________________ 1. Excel.Aplication Application.CreateInstance(L"Excel.Application", true); Application.Put(L"Visible", true); //_______________________________________________________________ 2. Aplication.Workbooks Com::Object Workbooks; Application.Get(L"Workbooks", Workbooks); //_______________________________________________________________ 3. Workbooks.Open //Com::Container::DisplayInterfaceFunctions(hWnd, Workbooks); Com::Object Workbook; Workbooks.Method(L"Open", filename, // _bstr_t Filename false, // [_variant_t UpdateLinks] true, // [_variant_t ReadOnly] // [_variant_t Format] // [_variant_t Password] // [_variant_t WriteResPassword] // [_variant_t IgnoreReadOnlyRecommended] // [_variant_t Origin], // [_variant_t Delimiter] // [_variant_t Editable], // [_variant_t Notify] // [_variant_t Converter] // [_variant_t AddToMru] // [_variant_t Local] // [_variant_t CorruptLoad]) returns USERDEFINED& Workbook); } catch(Com::Exception excep) { excep.Display(hWnd, L"Program"); Application.Method(L"Quit"); return false; } |
Delete cells |
| The following code illustrates how to delete cells. El código siguiente ilustra como borrar celdas. |
| Program.cpp |
| Excel::Range range; _variant_t delete = -4162; _variant_t first_cell = L"A2"; _variant_t last_cell = L"E2"; range = worksheet->get_Range(first_cell, last_cell); range.Select(); range.Delete(delete); |
| Tip |
| In some cases, the program performance can be improved by preventing Microsoft Excel from updating the screen while the program is running; in this case you may set the Application.Visible to false or Application.ScreenUpdating to false. En algunos casos, la velocidad del programa se puede mejorar previniendo que Microsoft Excel actualice la pantalla mientras el programa se ejecuta; en este caso usted puede fijar Application.Visible en false o Application.ScreenUpdating en false. |
| Tip |
| The Range object is very complex and has many options, you may search over the Internet to learn more about it. El objeto Range es muy complejo y tiene muchas opciones, usted puede buscar en la Internet para aprender más acerca de él. |
| Tip |
| Remember that variable of the data type _variant_t can store any data type, this includes empty. To know if a _variant_t variable is empty, you must use the .vt element of the variable. The code shown below checks if the variable x is empty. Recuerde que una variable del tipo de datos _variant_t puede almacenar cualquier tipo de datos, esto incluye a un valor vacío. Para saber si un valor _variant_t está vacío, usted puede usar el elemento .vt. El código de abajo checa si la variable x está vacía. |
| Program.cpp |
| _variant_t x; ... if (x.vt == VT_EMPTY) { // x is empty } |
| Tip |
| The following code shows how to convert some text to _variant_t. El siguiente código muestra como convertir un texto a _variant_t. |
| Program.cpp |
| //___________________________________________________ Using wchar_t wchar_t text[32]; _snwprintf_s(text, 32, _TRUNCATE, L"Number = %d", 5); _variant_t vtext = text; //___________________________________________________ Using wstring wstring wstr; Sys::Format(wstr, L"Number = %d", 5); _variant_t vstr = wstr.c_str(); |
| Problem 6 |
| Modify the program of Problem 1 to create the file shown. You may use Item.Formula to set the formula of each cell. Modifique el programa del Problema 1 para crear el archivo mostrado. Usted puede usar Item.Formula para fijar la fórmula de cada celda. |
| Tip |
| The file shown below illustrates how to use the import command to import Microsoft Excel. El archivo mostrado debajo ilustra cómo usar el comando import para importar Microsoft Excel. |
| Problem 7 |
| Create a Dialog application using Wintempla called ImportExcel. Edit the stdafx.h file to generate the files: excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli. Once the files have been generated, remove the #import command from the stdafx.h, move the respective files to your project and add them to the project using the menu Project > Add Existing Item... . Cree una aplicación de Diálogo usando Wintempla llamada ImportExcel. Edite el archivo stdafx.h para generar los archivos: excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli. Una vez que los archivos han sido generados, remueva el comando #import del archivo stdafx.h, mueva los archivos respectivo a su proyecto y agréguelos a su proyecto usando el menú Project > Add Existing Item... . |
| stdafx.h |
| ... // To generate excel.tlh, excel.tli, mso.tlh, mso.tli, vbe6ext.tlh, vbe6ext.tli #import "C:\\Program Files (x86)\\Microsoft Office\\Office12\\excel.exe" auto_search auto_rename dual_interfaces \ rename("IFont", "IFontx") \ rename("IPicture", "IPicturex") \ |
| ImportExcel.h |
| #pragma once //______________________________________ ImportExcel.h #include "Resource.h" #include "excel.tlh" #include "mso.tlh" #include "vbe6ext.tlh" class ImportExcel: public Win::Dialog { public: ImportExcel() { ::CoInitialize(NULL); } ~ImportExcel() { ::CoUninitialize(); } ... }; |
| ImportExcel.cpp |
| ... void ImportExcel::Window_Open(Win::Event& e) { Excel::_ApplicationPtr Application; HRESULT hr; Com::Exception ex; try { //______________________________________________________________ Create Excel Application hr = Application.CreateInstance(L"Excel.Application"); ex.ok(L"Application.CreateInstance", hr); //______________________________________________________________ Application->Visible Application->Visible[0] = VARIANT_TRUE; //______________________________________________________________ Workbooks->Add Excel::WorkbooksPtr Workbooks = Application->Workbooks; Excel::_WorkbookPtr Workbook = Workbooks->Add((long)Excel::xlWorksheet); ex.ok(L"Workbooks->Add", hr); //____________________________________________________________ Get the Active Sheet Excel::_WorksheetPtr WorkSheet = Application->ActiveSheet; WorkSheet->Name = "No. students"; // _______________________________________________________________ WorkSheet->Range["A1"][vtMissing]->Value2 = "Electronics"; WorkSheet->Range["B1"][vtMissing]->Value2 = "Algebra"; WorkSheet->Range["C1"][vtMissing]->Value2 = "SQL Databases"; WorkSheet->Range["D1"][vtMissing]->Value2 = "Basic C#"; // ______________________________________________________ WorkSheet->GetRange("A2")->Value2 = 20; WorkSheet->GetRange("B2")->Value2 = 50; WorkSheet->GetRange("C2")->Value2 = 10; WorkSheet->GetRange("D2")->Value2 = 30; //______________________________________________________ WorkSheet->GetRange("A2")->Font->ColorIndex = 3; WorkSheet->GetRange("B2")->Font->ColorIndex = 4; WorkSheet->GetRange("C2")->Font->ColorIndex = 5; WorkSheet->GetRange("D2")->Font->ColorIndex = 6; //______________________________________________________ WorkSheet->GetRange("A1")->HorizontalAlignment = (long)Excel::xlHAlignCenter; WorkSheet->GetRange("B1")->HorizontalAlignment = (long)Excel::xlHAlignCenter; WorkSheet->GetRange("C1")->HorizontalAlignment = (long)Excel::xlHAlignCenter; WorkSheet->GetRange("D1")->HorizontalAlignment = (long)Excel::xlHAlignCenter; // Excel::RangePtr Cells = WorkSheet->GetCells(); Excel::RangePtr LastCell = Cells->SpecialCells(Excel::XlCellType::xlCellTypeLastCell); long row = LastCell->Row; long col = LastCell->Column; wchar_t text[256]; _snwprintf_s(text, 256, _TRUNCATE, L"Rows = %d\r\nCols = %d", row, col); this->MessageBox(text, L"ImportExcel", MB_OK | MB_ICONINFORMATION); // Excel::_ChartPtr chart = Workbook->Charts->Add(); hr = chart->ChartWizard((Excel::Range*)Cells, (long)Excel::xl3DPie, 7L, (long)Excel::xlRows, 1L, 0L, 2L, "No. Students"); ex.ok(L"chart->ChartWizard", hr); ////WorkSheet->SaveAs(_bstr_t(filename), vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing); //Application->Quit(); } catch(Com::Exception& excep) { excep.Display(hWnd, L"ImportExcel"); } catch(_com_error excep) { Com::Exception::Display(hWnd, excep, L"ImportExcel"); } } |
